package net.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.dotool.Base64;
import net.dotool.ConnectionTools;
import net.dotool.page.PageModel;
import net.sql.bean.SqlParam;
import net.sql.config.CacheSqlPool;
import net.sql.config.SQLBean;
import net.sql.config.SQLConfig;
import net.sql.config.SQLFactory;
import net.sql.i.IDataRow;
import net.sql.i.IDbRow;

/**
 * @author 李岩飞
 * @email eliyanfei@126.com
 * @date 2018年7月20日上午9:55:35
 */
public class SqlConnector {
	private final SQLBean sqlBean;
	private final SqlParam sqlParam;
	private final SqlProcessor processor;
	private final String sql;
	private String hash;

	public SqlConnector(SQLBean sqlBean, SqlParam sqlParam) {
		this.sqlBean = sqlBean;
		this.sqlParam = sqlParam;
		this.sql = sqlBean.parserSQL(sqlParam);
		this.processor = new SqlProcessor(sqlBean);
		this.hash = Base64.encodeString(this.sql) + sqlParam.getParamList().hashCode();
	}

	public String getHash() {
		return hash;
	}

	public String getSql() {
		return sql;
	}

	/**
	 * 增删改 语句
	 */
	public int execute() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int rows = 0;
		try {
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());
			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rows = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
		return rows;
	}

	public Object queryKeyMap() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			CacheSqlPool sqlPool = SQLFactory.getConfig().getCachePool();
			if (sqlBean.isCache()) {
				Object data = sqlPool.getCacheData(hash);
				if (data != null)
					return data;
			}

			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			processor.header(rs.getMetaData());
			while (rs.next()) {
				processor.processKey(rs, null);
			}
			if (sqlBean.isCache()) {
				sqlPool.setCacheData(hash, processor.result());
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
		return processor.resultKey();
	}

	/**
	 * 查询Map
	 */
	public Object queryMap() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map<String, Object> dataMap = new HashMap<String, Object>();
		try {
			CacheSqlPool sqlPool = SQLFactory.getConfig().getCachePool();
			if (sqlBean.isCache()) {
				Object data = sqlPool.getCacheData(hash);
				if (data != null)
					return data;
			}
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				dataMap.put(rs.getString(1), rs.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
		return dataMap;
	}

	/**
	 * 普通查询
	 */
	public Object query() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			CacheSqlPool sqlPool = SQLFactory.getConfig().getCachePool();
			if (sqlBean.isCache()) {
				Object data = sqlPool.getCacheData(hash);
				if (data != null)
					return data;
			}
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			processor.header(rs.getMetaData());
			while (rs.next()) {
				processor.process(rs, null);
			}
			if (sqlBean.isCache()) {
				sqlPool.setCacheData(hash, processor.result());
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
		return processor.result();
	}

	public Object id() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
		return null;
	}

	public Object count() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getLong(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
		return null;
	}

	/**
	 * 普通自定义查询
	 */
	public void query(IDbRow row) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				row.row(rs);
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
	}

	/**
	 * 普通自定义查询
	 */
	public void query(IDataRow row) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			ps = conn.prepareStatement(sql);
			final List<Object> params = sqlParam.getParamList();
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			processor.header(rs.getMetaData());
			while (rs.next()) {
				processor.processNoCache(rs, row);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}
	}

	/**
	 * 分页查询
	 * 
	 */

	public Object queryPage(IDataRow row) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		PageModel pageModel = sqlParam.getPageModel();
		try {
			conn = SQLFactory.getConfig().getConnectionByBelong(sqlBean.getSource() + sqlParam.getBelong(),
					sqlBean.getDbName());

			final List<Object> params = sqlParam.getParamList();
			/* 非导出情况要获取分页和第一次查询 */
			if (!pageModel.isExcel() && (pageModel.getCount() == 0 || pageModel.isReload())) {
				StringBuffer count = new StringBuffer("select count(1) from (" + sql + ") t");
				// System.out.println("countSQL:" + count);
				ps = conn.prepareStatement(count.toString());
				for (int i = 0; i < params.size(); i++) {
					ps.setObject(i + 1, params.get(i));
				}
				rs = ps.executeQuery();
				while (rs.next()) {
					pageModel.setCount(rs.getLong(1));
				}
				ConnectionTools.closeAll(rs, ps, null);
			}
			String sqlPage = SQLFactory.getConfig().getPage(sqlBean.getDbName()).wrap(sql, pageModel);
			// System.out.println("sqlPage:" + sqlPage);
			ps = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
			}
			rs = ps.executeQuery();
			processor.header(rs.getMetaData());
			while (rs.next()) {
				processor.process(rs, row);
			}
			pageModel.setData(processor.result());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionTools.closeAll(rs, ps, conn);
		}

		return pageModel;
	}

	public Object queryPage() {
		return queryPage(null);
	}

	/**
	 * 只取第一条记录
	 */
	public Object queryOne() {
		List<?> list = (List<?>) query();
		if (list == null || list.isEmpty())
			return null;
		return list.get(0);
	}

}
